﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

namespace myShop
{
    public class OracleHelper
    {
        //私有变量，数据库连接
        //private SqlConnection Connection;
        private OracleConnection Connection;
        LogFile Log = new LogFile();

        //构造函数
        public OracleHelper()
		{
            
        }

        //保护方法，打开数据库连接
        private void Open()
        {
            string ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Global.o_server
                + ")(PORT=" + Global.o_port
                + "))(CONNECT_DATA=(SERVICE_NAME=" + Global.o_database
                + ")));Persist Security Info=True;User ID=" + Global.o_userid
                + ";Password=" + Global.o_password + ";";

            //判断数据库连接是否存在
            if (Connection == null)
            {
                //不存在，新建并打开
                //Connection = new SqlConnection(ConnectionString);
                Connection = new OracleConnection(ConnectionString);
                try
                {
                    Console.WriteLine("数据库连接:" + ConnectionString);

                    Connection.Open();
                    
                    //Log.doWriteLog(1, "数据库连接成功");
                }
                catch(Exception ex){
                    Console.WriteLine("数据库连接失败:" + ex.Message);
                    Log.doWriteLog(1, "oracle:" + ConnectionString);
                    Log.doWriteLog(1, "oracle连接失败:" + ex.Message);
                }

             }
            else
            {
                //存在，判断是否处于关闭状态
                if (Connection.State.Equals(ConnectionState.Closed))
                    Connection.Open();    //连接处于关闭状态，重新打开
            }

        }

        //公有方法，关闭数据库连接
        public void Close()
        {
            if (Connection.State.Equals(ConnectionState.Open))
            {
                Connection.Close();     //连接处于打开状态，关闭连接
            }
        }

        /// <summary>
		/// 析构函数，释放非托管资源
		/// </summary>
        ~OracleHelper()
		{
			try
			{
				if (Connection != null)
					Connection.Close();
			}
			catch{}
			try
			{
				Dispose();
			}
			catch{}
		}

        //公有方法，释放资源
        public void Dispose()
        {
            if (Connection != null)		// 确保连接被关闭
            {
                Connection.Dispose();
                Connection = null;
            }
        }

        //调用过程
        //参考：https://wenku.baidu.com/view/89333d055a0102020740be1e650e52ea5418ce46.html
        //      http://t.zoukankan.com/happylyyer-p-4346675.html
        public void doSQL_TO_CSV(string sql,string spath,string sfilename)
        {
            try
            {
                Open();
                Log.doWriteLog(1, "oracle导出:" + sql+","+ spath+","+sfilename);

                OracleCommand orm = Connection.CreateCommand();
                orm.CommandType = CommandType.StoredProcedure;
                orm.CommandText = "SQL_TO_CSV";
                orm.Parameters.Add("P_QUERY", OracleDbType.NVarchar2).Direction = ParameterDirection.Input;
                orm.Parameters["P_QUERY"].Value = sql.Trim();

                orm.Parameters.Add("P_DIR", OracleDbType.NVarchar2).Direction = ParameterDirection.Input;
                orm.Parameters["P_DIR"].Value = spath.Trim();

                orm.Parameters.Add("P_FILENAME", OracleDbType.NVarchar2).Direction = ParameterDirection.Input;
                orm.Parameters["P_FILENAME"].Value = sfilename.Trim();
                orm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Log.doWriteLog(1, e.Message);
                Dispose();
            }

        }
        
        //公有方法，根据Sql语句，返回一个Table
        public DataTable query(string sql)
        {
            DataSet Ds = new DataSet();
            try
            {
                Open();
                Log.doWriteLog(1, "oracle查询:" + sql);
                OracleDataAdapter OraDA = new OracleDataAdapter(sql, Connection);
                OraDA.Fill(Ds);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Log.doWriteLog(1,"oracle查询报错:"+ e.Message);
                Dispose();
            }
            return Ds.Tables[0];
        }

        //执行语句
        public void doExec(string sql)
        {
            try
            {
                Open();

                OracleTransaction trans = Connection.BeginTransaction();
                OracleCommand cmd = Connection.CreateCommand();
                cmd.Transaction = trans;

                try
                {
                    Log.doWriteLog(1, "oracle操作:" + sql);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    Console.WriteLine(e.Message);
                    Log.doWriteLog(1, e.Message + "\n" + sql);
                }

            }
            catch (Exception ex)
            {
                Log.doWriteLog(1, ex.Message);
            }

        }


        //数据保存
        public void doSave(out bool IsOk, out string sError)
        {
            IsOk = true;
            sError = "";

            try
            {
                Open();

                OracleTransaction trans = Connection.BeginTransaction();
                OracleCommand cmd = Connection.CreateCommand();
                cmd.Transaction = trans;

                //SqlTransaction trans = Connection.BeginTransaction();
                //SqlCommand cmd = new SqlCommand();
                //cmd.Connection = Connection;
                //cmd.Transaction = trans;

                //for (int i = 0; i < SQLList.Length; i++)
                foreach (string SQL in Global.SQLList)
                {

                    try
                    {
                        Log.doWriteLog(1,SQL);
                        cmd.CommandText = SQL;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        sError = e.Message;
                        Console.WriteLine(e.Message);
                        Log.doWriteLog(1, e.Message + "\n" + SQL);
                        IsOk = false;
                        break;
                    }
                }

                if (IsOk)
                    trans.Commit();
                else
                    trans.Rollback();
            }
            catch (Exception ex)
            {
                Log.doWriteLog(1, ex.Message);
            }

        }

                     
        //系统共用的方法
        public string getSpStr(string orgStr, string spliter, int index)
        {
            //根据分隔符截取字符
            int j, ii;
            j = 0;
            ii = 0;
            string sValue = "";
            for (int i = 0; i < orgStr.Length; i++)
            {
                sValue = orgStr.Substring(i, 1);
                if (sValue == spliter)
                {
                    if (j == index)
                    {
                        sValue = orgStr.Substring(ii, i - ii);
                        break;
                    }
                    j = j + 1;
                    ii = i + 1;
                }

            }

            return sValue;

        }

        private string getSPLineStr(string cStr, int toLen)
        {
            //产生分割符
            string sValue = string.Empty;

            for (int i = 0; i < toLen - 1; i++)
                sValue = sValue + cStr;

            return sValue;
        }

        private string getLengthStr(string org, int len, Boolean isLeft)
        {
            string tmpStr = string.Empty;
            string blank = string.Empty;

            //返回所需的长度
            int j = 0;
            for (int i = 0; i < org.Length; i++)
            {
                if ((int)org[i] > 127)
                    j = j + 2;
                else
                    j = j + 1;


                if (j <= len)
                    tmpStr = tmpStr + org[i];
                else
                    break;

            }

            //需要补充多少空格
            for (int k = 1; k <= len - j; k++)
            {
                blank = blank + " ";
            }

            //
            if (isLeft)
                blank = blank + tmpStr;
            else
                blank = tmpStr + blank;

            return blank;
        }
       
     
    }
}
